package cn.kgc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import cn.kgc.dao.BaseDao;
import cn.kgc.dao.NewsDao;
import cn.kgc.pojo.News;
import cn.kgc.pojo.NewsCategory;

//使用JDBC实现课工场新闻数据的增删改查
public class NewsDaoImpl extends BaseDao implements NewsDao{
	//根据CategoryId查询特定新闻信息
	public News getNewsByCategoryId(int categoryId) {
		News news = new News();
		String sql = "select title,summary,author,content,createdate,modifydate,picpath from news_detail where id=?";
		Object[] params = {categoryId};
		ResultSet rs = this.executeSQL(sql, params);
		try {
			while(rs.next()){
				String title = rs.getString("title");
				String summary = rs.getString("summary");
				String author = rs.getString("author");
				String content = rs.getString("content");
				Timestamp createdate = rs.getTimestamp("createdate");
				Timestamp modifydate = rs.getTimestamp("modifydate");
				String picpath = rs.getString("picpath");
				
				news.setTitle(title);
				news.setSummary(summary);
				news.setAuthor(author);
				news.setContent(content);
				news.setCreateDate(createdate);
				news.setModifyDate(modifydate);
				news.setPicPath(picpath);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return news;
	}
	
	//获取新闻总数量
	public int getTotalCount(){
		int total = 0;
		try {
			String sql = "select count(1) from news_detail";
			Object[] params = {};
			ResultSet rs = this.executeSQL(sql, params);
			while(rs.next()){
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}
		
	//分页获取新闻数据:pageNo 当前页码，pageSize 页面容量
	public List<News> getPageNewsList(int pageNo,int pageSize){
		List<News> list = new ArrayList<News>();
		String sql = "SELECT id,title,author,createdate FROM news_detail WHERE 1=1 ORDER BY createdate DESC LIMIT ?,?";
		Object[] params = {(pageNo-1)*pageSize,pageSize};
		ResultSet rs = this.executeSQL(sql, params);
		try {
			while(rs.next()){
				int id = rs.getInt("id");
				String title = rs.getString("title");
				String author = rs.getString("author");
				Timestamp createdate = rs.getTimestamp("createdate");
				
				News news = new News();
				news.setId(id);
				news.setTitle(title);
				news.setAuthor(author);
				news.setCreateDate(createdate);
				
				list.add(news);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	//根据ID删除特定新闻
	public boolean deleteNews2(int id) {
		boolean flag = false;
		String sql = "delete from news_detail where id=?";
		Object[] params = {id};
		int i = this.executeUpdate(sql, params);
		
		if(i>0){
			System.out.println("删除新闻成功！");
			flag = true;
		}
		return flag;
	}
	
	//根据ID修改特定新闻信息
	public boolean modifyNewsById(News news) {
		boolean flag = false;
		String sql = "Update news_detail set title=?,categoryid=?,summary=?,author=?,content=?,modifydate=?,picpath=? where id=?";
		Object[] params = {news.getTitle(),news.getCategoryId(),news.getSummary(),news.getAuthor(),news.getContent(),news.getModifyDate(),news.getPicPath(),news.getId()};
		
		int i = this.executeUpdate(sql, params);
		if(i>0){
			System.out.println("修改新闻成功！");
			flag = true;
		}
		return flag;
	}
	
	//根据ID查询特定新闻信息
	public News getNewsById(int id) {
		News news = new News();
		String sql = "select title,summary,author,content,createdate,modifydate,picpath from news_detail where id=?";
		Object[] params = {id};
		ResultSet rs = this.executeSQL(sql, params);
		try {
			while(rs.next()){
				String title = rs.getString("title");
				String summary = rs.getString("summary");
				String author = rs.getString("author");
				String content = rs.getString("content");
				Timestamp createdate = rs.getTimestamp("createdate");
				Timestamp modifydate = rs.getTimestamp("modifydate");
				String picpath = rs.getString("picpath");
				
				news.setTitle(title);
				news.setSummary(summary);
				news.setAuthor(author);
				news.setContent(content);
				news.setCreateDate(createdate);
				news.setModifyDate(modifydate);
				news.setPicPath(picpath);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return news;
	}
	
	//增加新闻信息	
	public boolean addNews(News news){
		boolean flag = false;
		try {
			String sql = "INSERT INTO news_detail(categoryid,title,summary,content,author,createDate,picpath) VALUES(?,?,?,?,?,?,?);";
			Object[] params = {news.getCategoryId(),news.getTitle(),news.getSummary(),news.getContent(),news.getAuthor(),news.getCreateDate(),news.getPicPath()};
			int i =this.executeUpdate(sql, params);	
			
			if(i>0){
				System.out.println("插入新闻成功！");
				flag = true;
			}
		}finally{			
			this.closeResource();
		}
		return flag;
	}
	
	//删除特定新闻的方法
	public void deleteNews1(News news){
		try {
			String sql = "DELETE FROM news_detail WHERE id=?";
			Object[] params = {news.getId()};
			int i = this.executeUpdate(sql, params);
			
			if(i>0){
				System.out.println("删除新闻成功！");
			}
		}finally{			
			this.closeResource();
		}
	}
	
	//修改特定新闻信息的方法
	public void updateNews(News news){
		try {
			this.getConnection();
			String sql = "UPDATE news_detail SET title=? WHERE id=?";
			Object[] params = {news.getTitle(),news.getId()};
			
			int i = this.executeUpdate(sql, params);	
			if(i>0){
				System.out.println("修改新闻成功！");
			}
		}finally{			
			this.closeResource();
		}
	}
	
	//通过数据源获取连接，进行数据库操作，查询新闻
	public void getNewsListByDS(){
		try { 
			String sql = "SELECT id,categoryid,title,summary,content,author,createDate FROM news_detail";
			Object[] params = {};
			ResultSet rs = this.executeSQL2(sql, params);
			
			while(rs.next()){
				int newsId = rs.getInt("id");
				int categoryId = rs.getInt("categoryid");
				String newsTitle = rs.getString("title");
				String summary = rs.getString("summary");
				String content = rs.getString("content");
				String author = rs.getString("author");
				Timestamp createdate = rs.getTimestamp("createDate");
				System.out.println(newsId+"\t"+categoryId+"\t"+newsTitle+"\t"+summary+"\t"+content+"\t"+author+"\t"+createdate);
			}			
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{			
			this.closeResource();
		}
	}
	
	//查询全部新闻信息
	public List<News> getNewsByList(){
		List<News> list = new ArrayList<News>();
		try { 
			String sql = "SELECT id,categoryid,title,summary,content,author,createDate FROM news_detail";
			Object[] params = {};
			ResultSet rs = this.executeSQL(sql, params);
			
			while(rs.next()){
				int id = rs.getInt("id");
				int categoryId = rs.getInt("categoryid");
				String title = rs.getString("title");
				String summary = rs.getString("summary");
				String content = rs.getString("content");
				String author = rs.getString("author");
				Timestamp createDate = rs.getTimestamp("createDate");
//				System.out.println(newsId+"\t"+categoryId+"\t"+newsTitle+"\t"+summary+"\t"+content+"\t"+author+"\t"+createdate);
				News news = new News();
				news.setId(id);
				news.setCategoryId(categoryId);
				news.setTitle(title);
				news.setSummary(summary);
				news.setContent(content);
				news.setAuthor(author);
				news.setCreateDate(createDate);
				list.add(news);
			}			
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{			
			this.closeResource();
		}
		 return list;
	}
	
	//查询特定标题的新闻信息
	public void getNewsByTitle(News news){
		try {
			String sql = "SELECT id,title FROM news_detail where title=?";
			Object[] params = {news.getTitle()};
			ResultSet rs = this.executeSQL(sql, params);
			
			while(rs.next()){
				int id = rs.getInt("id");
				String newsTitle = rs.getString("title");
				System.out.println(id+"\t"+newsTitle);
			}			
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{			
			this.closeResource();
		}
		 
	}
	
	//查询某个新闻分类下是否有新闻
	public int getCountByCategory(NewsCategory newsCategory) {
		int count = 0;
		try {
			String sql = "select count(1) from news_detail where categoryid = ?";
			Object[] params = {newsCategory.getId()};
			ResultSet rs = this.executeSQL(sql, params);
			while(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.closeResource();
		}
		return count;
	}
	
	public static void main(String[] args) {
		NewsDao dao = new NewsDaoImpl();
		//JavaBean添加新闻
		/*News news = new News();
		news.setId(88);
		news.setCategoryId(2);
		news.setTitle("test");
		news.setSummary("test");
		news.setContent("test");
		news.setAuthor("admin");
		news.setCreateDate(new Date());*/
		
		//JavaBean修改新闻
		/*News news = new News();
		news.setId(88);
		news.setTitle("newtest");
		dao.updateNews(news);*/
		
		//JavaBean删除新闻
		News news = new News();
		news.setId(88);
		dao.deleteNews1(news);
		
//		dao.addNews(news);
//		dao.getNewsByTitle("Java Web开课啦"); 
//		dao.getNewsByTitle("Java Web开课啦' or '1'='1");
		
//		dao.addNews(4, 1, "test", "test", "test", "test", new Date());
//		dao.updateNews(3, "newTitle");
		dao.getNewsByList();
	}
}
